const express = require('express')
const shop = express.Router()
const pool = require('../pool')
// 底部的导航栏新手指导,常见问题等相关的数据接口
shop.get('/guide', (req, res) => {
    let title = req.query.title
    let sql = 'SELECT class FROM hd_help_center where title=?'
    pool.query(sql, [title], (error, results) => {
        if (error) nexterror;
        res.send({ message: 'ok', code: 200, results: results });
    });
});

//评论分页查询路由
shop.get('/comments', (req, res,next) => {
    //获取客户端传递的分页数page
    //如果传过来是空值着默认为1页
    let page = req.query.page != 'null' ? req.query.page : 1;

    //每页存储的评论数量(默认为6个评论)
    let pagesize = 6;

    //通过公式来计算从第几条记录开始返回
    let offset = (page - 1) * pagesize

    //总页数
    let rowcount;

    //获取评论表里的评论总数sql语句
    let sql = 'select count(*) as count from hd_comments';
    pool.query(sql, (error, results) => {
        if (error) next(error);
        //获取到总数量赋给rowcount变量
        rowcount = results[0].count;
        //根据总评论数量除以，一页默认6条评论获取总页数
        let pagecount = Math.ceil(rowcount / pagesize)

        //查询评论分页Sql语句
        sql = 'select * from hd_comments limit ?,?';
        pool.query(sql, [offset, pagesize], (error, results) => {
            if (error) next(error);
            res.send({ message: 'ok', code: 200, results: results, page: page, pagecount: pagecount })
        })
    });
});
//商品列表路由
shop.get('/goods', (req, res,next) => {
    //如果传过来是空值着默认为1页
    let page = req.query.page != 'null' ? req.query.page : 1;
    //每页默认为12个商品卡片
    let pagesize = 15;
    //通过公式来计算从第几条记录开始返回
    let offset = (page - 1) * pagesize

    //保存总页数
    let rowcount;

    //查询出总条数
    let sql = `SELECT count(*) as count FROM hd_goods hg
			LEFT JOIN hd_picture hp
			ON hg.goods_id=hp.picture_id  AND hp.picture_Numbers=0
			LEFT JOIN hd_list_goods hl
			ON hg.goods_id=hl.list_id and hl.goods_Numbers=0`;
    pool.query(sql, (error, results) => {
        if (error) next(error);
        //获取到总数量赋给rowcount变量
        rowcount = results[0].count;
        //根据总评论数量除以，一页默认12条商品获取总页数
        let pagecount = Math.ceil(rowcount / pagesize)

        //多表分页查询
        sql = `SELECT hg.* , hp.picture_address,hl.goods_price FROM hd_goods hg
			LEFT JOIN hd_picture hp
			ON hg.goods_id=hp.picture_id  AND hp.picture_Numbers=0
			LEFT JOIN hd_list_goods hl
			ON hg.goods_id=hl.list_id and hl.goods_Numbers=0 limit ?,?`;
        pool.query(sql, [offset, pagesize], (error, results) => {
            if (error) nexterror;
            res.send({ message: 'ok', code: 200, page: page, pagecount: pagecount, results: results })
        })
    })
})


//商品详情表路由
shop.get('/details', (req, res) => {
    //获取页面传来的goods_id
    let goods_id = req.query.goods_id;

    //商品列表数据
    let hd_goods;
    //商品详情表数据
    let hd_list_goods;
    //商品图片表数据
    let hd_picture;

    //获取商品列表sql
    let sql = 'select * from hd_goods  where goods_id=?';
    pool.query(sql, [goods_id], (error, results) => {
        if (error) nexterror;
        //获取到的商品列表数据赋给hd_goods
        hd_goods = results;

        //获取商品详情表，商品颜色和商品价格sql	 
        sql = `select lid,goods_price,goods_color from hd_list_goods  where list_id=?;
	 select max(goods_price) as da ,min(goods_price) as xiao from hd_list_goods where list_id=?`;
        pool.query(sql, [goods_id, goods_id], (error, results) => {
            if (error) nexterror;
            //获取到的商品详情表数据赋给hd_list_goods
            hd_list_goods = results;

            //获取商品图片表，商品图片sql
            sql = 'select pid,picture_address from hd_picture  where picture_id=?';
            pool.query(sql, [goods_id], (error, results) => {
                if (error) nexterror;
                //获取到的商品图片表数据赋给hd_picture
                hd_picture = results;
                //成功响应
                res.send({ message: 'ok', code: 200, hd_goods: hd_goods, hd_list_goods: hd_list_goods, hd_picture: hd_picture })
            })
        })
    })
})

shop.get('/cs', (req, res) => {
    //获取页面传来的goods_id
    let goods_id = req.query.goods_id;
    //商品详情表数据
    let hd_list_goods;

    let sql = `select lid,goods_price,goods_color from hd_list_goods  where list_id=?;
	 select max(goods_price) as da ,min(goods_price) as xiao from hd_list_goods where list_id=?`;

    pool.query(sql, [goods_id, goods_id], (error, results) => {
        if (error) nexterror;
        //成功响应
        res.send({ message: 'ok', code: 200, results: results })
    })


})
module.exports = shop